package com.homestay.dao.impl;

import com.homestay.beans.Vip;
import com.homestay.dao.VipDao;
import com.homestay.utils.JdbcUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import java.sql.SQLException;
import java.util.Collections;
import java.util.List;

public class VipDaoImpl implements VipDao {

    QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());

    @Override
    public int add(Vip vip) {
        try {
            String sql = "insert into tb_vip values(null,?,?,?,?,?,?,?,now(),now())";
            return qr.update(sql, vip.getName(), vip.getSex(), vip.getCard(), vip.getPhone(), vip.getV_type(), vip.getStartTime(), vip.getEndTime());
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public List<Vip> queryByPhone(String phone) {
        List<Vip> vipList;
        try {
            String sql = "select * from tb_vip where phone like ?";
            vipList = qr.query(sql, new BeanListHandler<Vip>(Vip.class), "%" + phone + "%");
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return vipList;
    }

    @Override
    public List<Vip> queryByCard(String card) {
        try {
            String sql = "select * from tb_vip where card = ?";
            return qr.query(sql, new BeanListHandler<>(Vip.class), card);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public int deleteById(String id) {
        try {
            String sql = "delete from tb_vip where id = ?";
            return qr.update(sql, id);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public Vip queryById(String id) {
        try {
            String sql = "select * from tb_vip where id = ?";
            return qr.query(sql, new BeanHandler<>(Vip.class), id);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public int update(Vip vip) {
        try {
            String sql = "update tb_vip set name = ?,sex = ?,card = ?,phone = ?,v_type = ?,startTime = ?,endTime = ?,updateTime = now() where id = ?";
            return qr.update(sql, vip.getName(), vip.getSex(), vip.getCard(), vip.getPhone(), vip.getV_type(), vip.getStartTime(), vip.getEndTime(), vip.getId());
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public List<Vip> query(String name, String card, String phone) {
        try {
            String sql = "select * from tb_vip where name like ? and card like ? and phone like ?";
            return qr.query(sql, new BeanListHandler<>(Vip.class), "%" + name + "%", "%" + card + "%", "%" + phone + "%");
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

}
